﻿using System;
using System.Collections.Generic;
using System.Data;
using CoreLab.Oracle;
using LMD.Lib.Oracle;
using LMD.Lib.DataClass;

namespace Controller
{
    /// <summary>
    /// Summary description for Customer Controller
    /// </summary>
    public class NPSCustomerController : ControllerBase
    {

        public List<Entities.NPSCustomer> NPSCustomerList
        {
            get
            {
                return getNPSCustomerList(false);
            }
        }

        public List<Entities.NPSCustomer> NPSCustomerListwithInfo
        {
            get
            {
                return getNPSCustomerList(true);
            }
        }

        public List<Entities.NPSCustomer> getNPSCustomerList(string name, string company)
        {
            DBLib dbl = new DBLib();
            String sxQuery = @"select  
    cpc.company_pic_id,
    cpc.pic_name,
    cpc.GENDER,
    cpc.p_pic_position_id,
    pp.code as position_name,
    NVL (cpi1.INFO_DESC, '') email,
    NVL (cpi2.INFO_DESC, '') phone,
    NVL (cpi3.INFO_DESC, '') hp,
    cpc.company_id,
    cp.company_no,
    cp.company_name,
    cp.full_name,
    cs.P_company_segment_id,
    cs.code as company_segment,
    cc.P_customer_class_id,
    cc.code as company_class
    
from 
    sni.company_pic cpc
left join
    sni.company cp
    on cpc.company_id=cp.company_id
LEFT JOIN
    sni.p_pic_position pp
    ON cpc.p_pic_position_id = pp.p_pic_position_id

left join
    sni.p_customer_class cc
    on cp.p_customer_class_id=cc.P_customer_class_id
left join
    sni.p_company_segment cs
    on cp.p_company_segment_id=cs.p_company_segment_id
LEFT JOIN
     sni.COMPANY_PIC_INFO cpi1
     ON cpc.COMPANY_PIC_ID = cpi1.COMPANY_PIC_ID
     AND cpi1.P_INFO_TYPE_ID = 7
LEFT JOIN
     sni.COMPANY_PIC_INFO cpi2
     ON cpc.COMPANY_PIC_ID = cpi2.COMPANY_PIC_ID
     AND cpi2.P_INFO_TYPE_ID = 5                    
LEFT JOIN
     sni.COMPANY_PIC_INFO cpi3
     ON cpc.COMPANY_PIC_ID = cpi3.COMPANY_PIC_ID
     AND cpi3.P_INFO_TYPE_ID = 23            
where
    NVL (cpc.VALID_TO, SYSDATE) >= SYSDATE and upper(cpc.pic_name) like upper('%" + name + "%') and upper(cp.company_name) like upper('%" + company + "%') and rownum<=20";
            DataTable dt = dbl.GetDataTable(sxQuery);

            List<Entities.NPSCustomer> CustomerList = new List<Entities.NPSCustomer>();
            foreach (DataRow row in dt.Rows)
            {
                Entities.NPSCustomer ciCustomer = new Entities.NPSCustomer();
                ciCustomer.iID = Convert.ToInt32(row["company_pic_id"].ToString());
                ciCustomer.iCompanyClassID = Convert.ToInt32(row["P_customer_class_id"].ToString());
                ciCustomer.iCompanyID = Convert.ToInt32(row["company_id"].ToString());
                ciCustomer.iCompanyNo = Convert.ToInt32(row["company_no"].ToString());
                ciCustomer.iCompanySegmentID = Convert.ToInt32(row["P_company_segment_id"].ToString());
                ciCustomer.iPositionID = Convert.ToInt32(row["p_pic_position_id"].ToString());
                ciCustomer.sxCompanyClass = row["company_class"].ToString();
                ciCustomer.sxCompanyFullName = row["full_name"].ToString();
                ciCustomer.sxCompanyName = row["company_name"].ToString();
                ciCustomer.sxCompanySegment = row["company_segment"].ToString();
                ciCustomer.sxEmail = row["email"].ToString();
                ciCustomer.sxGender = row["GENDER"].ToString();
                ciCustomer.sxHP = row["hp"].ToString();
                ciCustomer.sxPhone = row["phone"].ToString();
                ciCustomer.sxPICName = row["pic_name"].ToString();
                ciCustomer.sxPosition = row["position_name"].ToString();
                ciCustomer.isNPSInfoIncluded = false;
                CustomerList.Add(ciCustomer);
            }
            return CustomerList;
        }

        protected List<Entities.NPSCustomer> getNPSCustomerList(bool isDetailed)
        {
            DBLib dbl = new DBLib();
            String sxQuery = @"select  
    cpc.company_pic_id,
    cpc.pic_name,
    cpc.GENDER,
    cpc.p_pic_position_id,
    pp.code as position_name,
    NVL (cpi1.INFO_DESC, '') email,
    NVL (cpi2.INFO_DESC, '') phone,
    NVL (cpi3.INFO_DESC, '') hp,
    cpc.company_id,
    cp.company_no,
    cp.company_name,
    cp.full_name,
    cs.P_company_segment_id,
    cs.code as company_segment,
    cc.P_customer_class_id,
    cc.code as company_class
    
from 
    sni.company_pic cpc
left join
    sni.company cp
    on cpc.company_id=cp.company_id
LEFT JOIN
    sni.p_pic_position pp
    ON cpc.p_pic_position_id = pp.p_pic_position_id

left join
    sni.p_customer_class cc
    on cp.p_customer_class_id=cc.P_customer_class_id
left join
    sni.p_company_segment cs
    on cp.p_company_segment_id=cs.p_company_segment_id
LEFT JOIN
     sni.COMPANY_PIC_INFO cpi1
     ON cpc.COMPANY_PIC_ID = cpi1.COMPANY_PIC_ID
     AND cpi1.P_INFO_TYPE_ID = 7
LEFT JOIN
     sni.COMPANY_PIC_INFO cpi2
     ON cpc.COMPANY_PIC_ID = cpi2.COMPANY_PIC_ID
     AND cpi2.P_INFO_TYPE_ID = 5                    
LEFT JOIN
     sni.COMPANY_PIC_INFO cpi3
     ON cpc.COMPANY_PIC_ID = cpi3.COMPANY_PIC_ID
     AND cpi3.P_INFO_TYPE_ID = 23            
where
    NVL (cpc.VALID_TO, SYSDATE) >= SYSDATE and rownum<=10";
            DataTable dt = dbl.GetDataTable(sxQuery);

            List<Entities.NPSCustomer> CustomerList = new List<Entities.NPSCustomer>();
            foreach (DataRow row in dt.Rows)
            {
                Entities.NPSCustomer ciCustomer = new Entities.NPSCustomer();
                ciCustomer.iID = Convert.ToInt32(row["company_pic_id"].ToString());
                ciCustomer.iCompanyClassID = Convert.ToInt32(row["P_customer_class_id"].ToString());
                ciCustomer.iCompanyID = Convert.ToInt32(row["company_id"].ToString());
                ciCustomer.iCompanyNo = Convert.ToInt32(row["company_no"].ToString());
                ciCustomer.iCompanySegmentID = Convert.ToInt32(row["P_company_segment_id"].ToString());
                ciCustomer.iPositionID = Convert.ToInt32(row["p_pic_position_id"].ToString());
                ciCustomer.sxCompanyClass = row["company_class"].ToString();
                ciCustomer.sxCompanyFullName = row["full_name"].ToString();
                ciCustomer.sxCompanyName = row["company_name"].ToString();
                ciCustomer.sxCompanySegment = row["company_segment"].ToString();
                ciCustomer.sxEmail = row["email"].ToString();
                ciCustomer.sxGender = row["GENDER"].ToString();
                ciCustomer.sxHP = row["hp"].ToString();
                ciCustomer.sxPhone = row["phone"].ToString();
                ciCustomer.sxPICName = row["pic_name"].ToString();
                ciCustomer.sxPosition = row["position_name"].ToString();
                ciCustomer.isNPSInfoIncluded = false;
                CustomerList.Add(ciCustomer);
            }
            return CustomerList;
        }

        public Entities.NPSCustomer getNPSCustomer(int CustomerID, bool NPSInfo)
        {
            DBLib dbl = new DBLib();
            String sxQuery = @"
select  
    cpc.company_pic_id,
    cpc.pic_name,
    cpc.GENDER,
    cpc.p_pic_position_id,
    pp.code as position_name,
    NVL (cpi1.INFO_DESC, '') email,
    NVL (cpi2.INFO_DESC, '') phone,
    NVL (cpi3.INFO_DESC, '') hp,
    cpc.company_id,
    cp.company_no,
    cp.company_name,
    cp.full_name,
    cs.P_company_segment_id,
    cs.code as company_segment,
    cc.P_customer_class_id,
    cc.code as company_class
    
from 
    sni.company_pic cpc
left join
    sni.company cp
    on cpc.company_id=cp.company_id
LEFT JOIN
    sni.p_pic_position pp
    ON cpc.p_pic_position_id = pp.p_pic_position_id
left join
    sni.p_customer_class cc
    on cp.p_customer_class_id=cc.P_customer_class_id
left join
    sni.p_company_segment cs
    on cp.p_company_segment_id=cs.p_company_segment_id
LEFT JOIN
     sni.COMPANY_PIC_INFO cpi1
     ON cpc.COMPANY_PIC_ID = cpi1.COMPANY_PIC_ID
     AND cpi1.P_INFO_TYPE_ID = 7
LEFT JOIN
     sni.COMPANY_PIC_INFO cpi2
     ON cpc.COMPANY_PIC_ID = cpi2.COMPANY_PIC_ID
     AND cpi2.P_INFO_TYPE_ID = 5                    
LEFT JOIN
     sni.COMPANY_PIC_INFO cpi3
     ON cpc.COMPANY_PIC_ID = cpi3.COMPANY_PIC_ID
     AND cpi3.P_INFO_TYPE_ID = 23 

where
    NVL (cpc.VALID_TO, SYSDATE) >= SYSDATE and cpc.company_pic_id=:prmID";
            List<OracleParameter> lsParams = new List<OracleParameter>();
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = CustomerID;
            DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());

            DataRow row = dt.Rows[0];
            dt.Dispose();
            Entities.NPSCustomer ciCustomer = new Entities.NPSCustomer();
            try
            {
                ciCustomer.iID = Convert.ToInt32(row["company_pic_id"].ToString());
                ciCustomer.iCompanyClassID = Convert.ToInt32(row["P_customer_class_id"].ToString());
                ciCustomer.iCompanyID = Convert.ToInt32(row["company_id"].ToString());
                ciCustomer.iCompanyNo = Convert.ToInt32(row["company_no"].ToString());
                ciCustomer.iCompanySegmentID = Convert.ToInt32(row["P_company_segment_id"].ToString());
                ciCustomer.iPositionID = Convert.ToInt32(row["p_pic_position_id"].ToString());
                ciCustomer.sxCompanyClass = row["company_class"].ToString();
                ciCustomer.sxCompanyFullName = row["full_name"].ToString();
                ciCustomer.sxCompanyName = row["company_name"].ToString();
                ciCustomer.sxCompanySegment = row["company_segment"].ToString();
                ciCustomer.sxEmail = row["email"].ToString();
                ciCustomer.sxGender = row["GENDER"].ToString();
                ciCustomer.sxHP = row["hp"].ToString();
                ciCustomer.sxPhone = row["phone"].ToString();
                ciCustomer.sxPICName = row["pic_name"].ToString();
                ciCustomer.sxPosition = row["position_name"].ToString();
                if (NPSInfo)
                {
                    try
                    {
                        ciCustomer.isNPSInfoIncluded = true;
                        Controller.NPSSurveyController sc = new NPSSurveyController();
                        ciCustomer.oNPSInfo = sc.getNPSSurveyList(ciCustomer.iID);
                    }
                    catch { ciCustomer.isNPSInfoIncluded = false; }
                }
                else
                {
                    ciCustomer.isNPSInfoIncluded = false;
                }
            }
            catch (Exception p) { errLog = p.ToString(); }


            return ciCustomer;
        }


        
    }
}